
import pandas as pd
from win32com.client import Dispatch
import win32com.client

import xlrd
from xlrd import xldate_as_tuple
import datetime


class ExcelData():
    def __init__(self, data_path, sheetname):
        self.data_path = data_path
        self.sheetname = sheetname
        self.data = xlrd.open_workbook(self.data_path)
        self.table = self.data.sheet_by_name(self.sheetname)
        self.keys = self.table.row_values(0)
        self.rowNum = self.table.nrows
        self.colNum = self.table.ncols

    def readExcel(self):
        datas = []
        for i in range(1, self.rowNum):
            sheet_data = {}
            for j in range(self.colNum):
                c_type = self.table.cell(i, j).ctype
                c_cell = self.table.cell_value(i, j)
                if c_type == 2 and c_cell % 1 == 0:
                    c_cell = int(c_cell)
                elif c_type == 3:
                    date = datetime.datetime(*xldate_as_tuple(c_cell, 0))
                    c_cell = date.strftime('%Y/%d/%m %H:%M:%S')
                elif c_type == 4:
                    c_cell = True if c_cell == 1 else False
                sheet_data[self.keys[j]] = c_cell
            datas.append(sheet_data)
        return datas



class easyExcel():

    def __init__(self, filename=None):  # 打开文件或者新建文件（如果不存在的话）
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def save(self, newfilename=None):  # 保存文件
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):  # 关闭文件
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp


def excel_many_copy(data_path, file_excel, num, sheetname="Sheet1"):

    get_data = ExcelData(data_path, sheetname)
    excel_data = get_data.readExcel()[0]

    s = [list(excel_data.values()) for i in range(num)]
    excel_new_data = dict(map(lambda x, y: [x, y], excel_data.keys(), zip(*s)))

    info_markes = pd.DataFrame(excel_new_data)
    writer = pd.ExcelWriter(file_excel)
    info_markes.to_excel(writer, sheet_name=sheetname, index=False)
    writer.save()

    # 需要通过 win32com保存一次Excel（大坑）
    xls = easyExcel(file_excel)
    xls.save()
    xls.close()


import os

if __name__ == "__main__":
    data_path = r'example.xlsx'
    base_dir = os.path.dirname(os.path.abspath(__file__))  # 获取当前路径
    file_excel = base_dir + '/filename123.xlsx'
    excel_many_copy(data_path, file_excel, 2)